Reel 2 Reel 2006 - how does it work

Reel 2 Reel is a relational database management system. Knowing about relational database concepts makes it much easier to work with database applications and helps to understand both, benefits and limitations.

Wolfgang Bleier, Austria
September 2006


Summary

The simplified schematic diagram below shows the general design of relationships between the tables (primary and related tables) of Reel to Reel 2006 ©.

Each table in Reel to Reel 2006 © has one or more fields with primary key property, which requires input of unique values. The tables of Reel to Reel 2006 © are in relationships as shown below, some with enforced referential integrity in order to apply rules for secure data processing and to preserve integrity and consistency of the data. The relationships between the tables of Reel to Reel 2006 © are ONE to MANY relationships. That means for example: while the code of a music genre can exist only once in the primary table MUSIC GENRES, it can be saved many times in the related tables RECORD ARCHIVES, AUDIO CASSETTE RECORDINGS and OPEN REEL TAPE RECORDINGS.
Whether a table is considered a primary or related table depends on its relationships with other tables. For example, although RECORD ARCHIVES is the primary table in relation to the related table SONGS ON RECORD, it is at the same time a related table in relation to the primary table MUSIC GENRES.


Reel to Reel 2006 © - simplified schematic diagram



Interested users can find more details about primary key, relationships and data integrity below or in the relevant Microsoft Access help topics.



About Primary Key Fields

Tables and Primary Key fields are fundamental elements in good database design. It is the strength of relational database systems to query information, which is saved by category in different tables, very fast. In order to achieve efficient query of data, each table in a database should have one field, which uniquely identifies each dataset saved in a table.

A field, which requires unique values is called primary key (or it can also be a combined primary key from several fields, like the "Cassette ID" in the form AUDIO CASSETTE RECORDINGS, which combines the "Genre", the "Cassette No." and the "Cassette's Side" A or B to a primary key).

In other words, a primary key does not accept duplicates of values that already exist in this field in other data sets. In practice this means, that you cannot save a data set when it contains a duplicate in the primary key, for example the tape number in the dataset of an open reel tape, when you have used this tape number already earlier for another tape. In the event that you try to save a dataset with a duplicate in the primary key, the database will prevent you from doing so and display a message. As a general rule in Reel to Reel 2006 ©, fields with PRIMARY KEY property display the data in red color.

About Tables and Relationships

The relationships between the tables of Reel to Reel2006 © are mainly ONE to MANY relationships. That means for example: while you can define the code of a music genre only once in the primary table MUSIC GENRES, you can save this code many times in the related tables RECORD ARCHIVES, AUDIO CASSETTE RECORDINGS and OPEN REEL TAPE RECORDINGS. Or, while the primary table RECORD ARCHIVES can contain a unique Archive No. of a certain record only once, this Archive No. is used many times in the related table SONGS ON RECORD (which is necessary to correctly relate songs to a certain record of a CD or LP). Such relationships by the way work automatically in the background and you don’t need to do anything else than filling your data into the forms.
However, it is good to have knowledge about relationships in a database, especially when it comes to adding, deleting or changing data, which are in relationships to other tables with enforced referential integrity.

About referential integrity

Referential integrity enforces rules that you are to follow to preserve the defined relationships between tables when you enter or delete records.
When referential integrity is enforced, Microsoft Access prevents you from adding records to a related table when there is no associated record in the primary table, changing values in the primary table that would result in orphan records in a related table, and deleting records from the primary table when there are matching related records in a related table.

What is referential integrity ?

Referential integrity is a system of rules that a database in Microsoft Access uses to ensure that relationships between records in related tables are valid, and that you don't accidentally delete or change related data.

When referential integrity is enforced, you must observe the following rules:

• You can't enter a value in the foreign key field of the related table that doesn't exist in the primary key of the primary table. For example, you can’t save a (new) code of a music genre in the related AUDIO CASSETTE table, if this code does not exist yet in the primary table MUSIC GENRES. You must establish such code in the primary table (MUSIC GENRES) before you can use it in the related tables (e.g. AUDIO CASSETTES).
• You can't delete a record from a primary table if matching records exist in a related table. For example, you can't delete a record (Tape) from the OPEN REEL TAPE RECORDINGS primary table if there are songs in the related table SONGS ON TAPE still assigned to this tape. In such case you must first delete the songs assigned to this tape, only then you can finally delete the tape itself.
• Normally you can't change a primary key value in the primary table, if that data set has related values in a related table. However, where appropriate Reel to Reel 2006 © has relationships that override this restriction by relating such tables in a "Cascade Update Relationship". This allows you to change a primary key in the primary table and still preserve referential integrity, because Reel to Reel 2006 © automatically updates the matching value in all related tables.
  If, for example, you have to re-organize your genres in the primary table MUSIC GENRES and you have to change the code of a genre that you have used before in the related tables RECORD ARCHIVES, OPEN REEL TAPE RECORDINGS and/or AUDIO CASSETTE RECORDINGS, Reel to Reel 2006 © will automatically update these related tables with the new code of that certain genre. Or, if for any reason you give one of your Open Reel Tapes a new number, the songs assigned to this tape will not become lost orphans but will be immediately assigned to the new tape number because Reel to Reel 2006 © automatically updates the new Tape Number of the primary table OPEN REEL TAPE RECORDINGS in the related table SONGS ON TAPE. Also the relationship between the primary table RECORD ARCHIVES (LPs and CDs) and the related table SONGS ON RECORD is a cascade update relationship.

Reel to Reel 2006 © tables have referential integrity enforced in various ways as appropriate. You don't have to learn these rules, but you should be aware that these exist. If you break one of the rules with related tables, Reel to Reel 2006 © will anyway display a message and will not allow the change.

So far it sounds all right in theory. Before you will start to fill in real data in Reel to Reel 2006 ©, I recommend that you make a copy of Reel to Reel 2006 © and do some exercise by filling in, changing and deleting data in order to become acquainted with the rules of Reel to Reel 2006 ©, or you can download the Reel to Reel 2006 TRIAL database to give it a burst right away.